package com.demo.crm.springboot3security.service.impl;

import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.stream.Collectors;

import org.springframework.cache.annotation.CacheEvict;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.demo.crm.springboot3security.entity.CustomForm;
import com.demo.crm.springboot3security.entity.FormField;
import com.demo.crm.springboot3security.entity.FormFieldValue;
import com.demo.crm.springboot3security.entity.FormRelation;
import com.demo.crm.springboot3security.entity.FormTableStructure;
import com.demo.crm.springboot3security.mapper.FormFieldValueMapper;
import com.demo.crm.springboot3security.mapper.FormTableStructureMapper;
import com.demo.crm.springboot3security.service.FormDataService;
import com.demo.crm.springboot3security.validation.FormDataValidator;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.mybatisflex.core.paginate.Page;
import com.mybatisflex.core.query.QueryWrapper;

import io.micrometer.core.annotation.Timed;
import io.micrometer.core.instrument.MeterRegistry;
import io.micrometer.core.instrument.Timer;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;

@Slf4j
@Service
@RequiredArgsConstructor
public class FormDataServiceImpl implements FormDataService {

    private final JdbcTemplate jdbcTemplate;
    private final FormTableStructureMapper tableStructureMapper;
    private final FormFieldValueMapper fieldValueMapper;
    private final FormDataValidator validator;
    private final ObjectMapper objectMapper;
    private final MeterRegistry meterRegistry;

    @Override
    @Transactional
    @Timed(value = "form.data.save", description = "保存表单数据的耗时")
    @CacheEvict(value = { "formData", "formQuery", "formAggregate", "formRelated" }, allEntries = true)
    public Long saveFormData(CustomForm form, Map<String, Object> data) {
        Timer.Sample sample = Timer.start(meterRegistry);
        try {
            // 数据验证
            validator.validate(form, data);

            // 获取表结构
            Optional<FormTableStructure> opt = Optional.ofNullable(tableStructureMapper.findByFormIdAndStatus(form.getId(), "ACTIVE"));
            if (opt.isEmpty()) {
                throw new RuntimeException("表单未初始化");
            }
            FormTableStructure structure = opt.get();

            // 分离数据
            Map<String, Object> commonFields = new LinkedHashMap<>();
            Map<String, Object> specialFields = new LinkedHashMap<>();

            for (FormField field : form.getFields()) {
                Object value = data.get(field.getCode());
                if (value == null)
                    continue;

                if (isCommonField(field)) {
                    commonFields.put(field.getCode(), value);
                } else {
                    specialFields.put(field.getCode(), value);
                }
            }

            // 保存到动态表
            String sql = generateInsertSql(structure.getTableName(), commonFields);
            Long recordId = jdbcTemplate.queryForObject(sql, Long.class, commonFields.values().toArray());

            // 保存特殊字段
            if (!specialFields.isEmpty()) {
                saveFieldValues(form, recordId, specialFields);
            }

            return recordId;
        } finally {
            sample.stop(Timer.builder("form.data.save.timer")
                    .description("保存表单数据耗时")
                    .tag("form", form.getCode())
                    .register(meterRegistry));
        }
    }

    @Override
    @Transactional
    public void updateFormData(CustomForm form, Long recordId, Map<String, Object> data) {
        // 1. 获取表结构
        Optional<FormTableStructure> opt = Optional.ofNullable(tableStructureMapper.findByFormIdAndStatus(form.getId(), "ACTIVE"));
        if (opt.isEmpty()) {
            throw new RuntimeException("表单未初始化");
        }
        FormTableStructure structure = opt.get();

        // 2. 分离字段数据
        LinkedHashMap<String, Object> commonFields = new LinkedHashMap<>();
        LinkedHashMap<String, Object> specialFields = new LinkedHashMap<>();
        LinkedHashMap<String, Object> extraData = new LinkedHashMap<>();

        for (FormField field : form.getFields()) {
            String fieldCode = field.getCode();
            Object value = data.get(fieldCode);

            if (value == null)
                continue;

            if (isCommonField(field)) {
                commonFields.put(fieldCode, value);
            } else {
                specialFields.put(fieldCode, value);
            }
        }

        // 3. 更新动态表
        if (!commonFields.isEmpty()) {
            String sql = generateUpdateSql(structure.getTableName(), commonFields, recordId);
            Object[] array = commonFields.values().toArray();
            jdbcTemplate.update(sql, array);
            // jdbcTemplate.update(sql);
        }

        // 4. 更新特殊字段
        if (!specialFields.isEmpty()) {
            // 删除旧值
            fieldValueMapper.deleteByFormIdAndRecordId(form.getId(), recordId);
            // 保存新值
            saveFieldValues(form, recordId, specialFields);
        }

        // 5. 更新额外数据
        if (!extraData.isEmpty()) {
            updateExtraData(form, recordId, extraData);
        }
    }

    @Override
    @Cacheable(value = "formData", key = "#form.id + '_' + #recordId", unless = "#result == null")
    @Timed(value = "form.data.get", description = "获取表单数据的耗时")
    public Map<String, Object> getFormData(CustomForm form, Long recordId) {
        Timer.Sample sample = Timer.start(meterRegistry);
        try {
            Optional<FormTableStructure> opt = Optional.ofNullable(tableStructureMapper.findByFormIdAndStatus(form.getId(), "ACTIVE"));
            if (opt.isEmpty()) {
                throw new RuntimeException("表单未初始化");
            }
            FormTableStructure structure = opt.get();

            String sql = String.format("SELECT * FROM %s WHERE id = ?", structure.getTableName());
            Map<String, Object> result = jdbcTemplate.queryForMap(sql, recordId);

            // 查询特殊字段
            List<FormFieldValue> fieldValues = fieldValueMapper.findByFormIdAndRecordId(form.getId(), recordId);
            for (FormFieldValue value : fieldValues) {
                result.put(value.getField().getCode(), getFieldValue(value));
            }

            // 记录结果大小
            meterRegistry.counter("form.data.size",
                    "form", form.getCode()).increment(result.size());

            return result;
        } finally {
            sample.stop(Timer.builder("form.data.get.timer")
                    .description("获取表单数据耗时")
                    .tag("form", form.getCode())
                    .register(meterRegistry));
        }
    }

    @Override
    @Transactional
    public void deleteFormData(CustomForm form, Long recordId) {
        // 1. 获取表结构
        Optional<FormTableStructure> opt = Optional.ofNullable(tableStructureMapper.findByFormIdAndStatus(form.getId(), "ACTIVE"));
        if (opt.isEmpty()) {
            throw new RuntimeException("表单未初始化");
        }
        FormTableStructure structure = opt.get();

        // 2. 删除动态表数据
        String sql = String.format("DELETE FROM %s WHERE id = ?", structure.getTableName());
        jdbcTemplate.update(sql, recordId);

        // 3. 删除特殊字段数据
        fieldValueMapper.deleteByFormIdAndRecordId(form.getId(), recordId);
    }

    @Override
    public List<Map<String, Object>> queryFormData(CustomForm form, Map<String, Object> conditions, int page,
            int size) {
        // 1. 获取表结构
        Optional<FormTableStructure> opt = Optional.ofNullable(tableStructureMapper.findByFormIdAndStatus(form.getId(), "ACTIVE"));
        if (opt.isEmpty()) {
            throw new RuntimeException("表单未初始化");
        }
        FormTableStructure structure = opt.get();

        // 2. 生成查询SQL
        StringBuilder sql = new StringBuilder()
                .append("SELECT * FROM ").append(structure.getTableName())
                .append(" WHERE 1=1");

        List<Object> params = new ArrayList<>();

        // 3. 添加查询条件
        if (conditions != null && !conditions.isEmpty()) {
            for (Map.Entry<String, Object> entry : conditions.entrySet()) {
                sql.append(" AND ").append(entry.getKey()).append(" = ?");
                params.add(entry.getValue());
            }
        }

        // 4. 添加分页
        sql.append(" LIMIT ? OFFSET ?");
        params.add(size);
        params.add((page - 1) * size);

        // 5. 执行查询
        return jdbcTemplate.queryForList(sql.toString(), params.toArray());
    }

    @Override
    @Timed(value = "form.data.query.advanced", description = "高级查询的耗时")
//    @Cacheable(value = "formQuery", key = "#form.id + '_' + #conditions + '_' + #orderBy + '_' + #page + '_' + #size", unless = "#result.content.isEmpty()")
    public Page<Map<String, Object>> advancedQuery(
            CustomForm form,
            Map<String, Object> conditions,
            Map<String, String> orderBy,
            int page,
            int size) {

        Timer.Sample sample = Timer.start(meterRegistry);
        try {
            Optional<FormTableStructure> opt = Optional.ofNullable(tableStructureMapper.findByFormIdAndStatus(form.getId(), "ACTIVE"));
            if (opt.isEmpty()) {
                throw new RuntimeException("表单未初始化");
            }
            FormTableStructure structure = opt.get();

            // 构建查询SQL
            StringBuilder sql = new StringBuilder()
                    .append("SELECT * FROM ").append(structure.getTableName())
                    .append(" WHERE 1=1");

            List<Object> params = new ArrayList<>();

            // 处理查询条件
            if (conditions != null) {
                for (Map.Entry<String, Object> entry : conditions.entrySet()) {
                    String key = entry.getKey();
                    Object value = entry.getValue();

                    if (key.endsWith("_like")) {
                        String field = key.substring(0, key.length() - 5);
                        sql.append(" AND ").append(field).append(" LIKE ?");
                        params.add("%" + value + "%");
                    } else if (key.endsWith("_gt")) {
                        String field = key.substring(0, key.length() - 3);
                        sql.append(" AND ").append(field).append(" > ?");
                        params.add(value);
                    } else if (key.endsWith("_lt")) {
                        String field = key.substring(0, key.length() - 3);
                        sql.append(" AND ").append(field).append(" < ?");
                        params.add(value);
                    } else if (key.endsWith("_gte")) {
                        String field = key.substring(0, key.length() - 4);
                        sql.append(" AND ").append(field).append(" >= ?");
                        params.add(value);
                    } else if (key.endsWith("_lte")) {
                        String field = key.substring(0, key.length() - 4);
                        sql.append(" AND ").append(field).append(" <= ?");
                        params.add(value);
                    } else {
                        sql.append(" AND ").append(key).append(" = ?");
                        params.add(value);
                    }
                }
            }

            // 添加排序
            if (orderBy != null && !orderBy.isEmpty()) {
                sql.append(" ORDER BY ");
                sql.append(orderBy.entrySet().stream()
                        .map(e -> e.getKey() + " " + e.getValue())
                        .collect(Collectors.joining(", ")));
            }

            // 计算总数
            String countSql = "SELECT COUNT(*) FROM (" + sql.toString() + ") t";
            Long total = jdbcTemplate.queryForObject(countSql, Long.class, params.toArray());

            // 添加分页
            sql.append(" LIMIT ? OFFSET ?");
            params.add(size);
            params.add((page - 1) * size);

            // 执行查询
            List<Map<String, Object>> content = jdbcTemplate.queryForList(sql.toString(), params.toArray());

            // 记录查询结果大小
            meterRegistry.counter("form.query.result.size",
                    "form", form.getCode()).increment(content.size());

            Page<Map<String, Object>> ret = new Page<>(page, size, total);
            ret.setRecords(content);
            return ret;
        } finally {
            sample.stop(Timer.builder("form.data.query.timer")
                    .description("查询表单数据耗时")
                    .tag("form", form.getCode())
                    .register(meterRegistry));
        }
    }

    @Override
    @Timed(value = "form.data.aggregate", description = "统计分析的耗时")
    @Cacheable(value = "formAggregate", key = "#form.id + '_' + #groupBy + '_' + #aggregates + '_' + #conditions")
    public List<Map<String, Object>> aggregate(
            CustomForm form,
            List<String> groupBy,
            Map<String, String> aggregates,
            Map<String, Object> conditions) {

        Optional<FormTableStructure> opt = Optional.ofNullable(tableStructureMapper.findByFormIdAndStatus(form.getId(), "ACTIVE"));
        if (opt.isEmpty()) {
            throw new RuntimeException("表单未初始化");
        }
        FormTableStructure structure = opt.get();

        // 构建查询SQL
        StringBuilder sql = new StringBuilder().append("SELECT ");

        // 添加分组字段
        if (groupBy != null && !groupBy.isEmpty()) {
            sql.append(String.join(", ", groupBy)).append(", ");
        }

        // 添加聚合函数
        sql.append(aggregates.entrySet().stream()
                .map(e -> String.format("%s(%s) as %s_%s",
                        e.getValue(), e.getKey(), e.getValue(), e.getKey()))
                .collect(Collectors.joining(", ")));

        sql.append(" FROM ").append(structure.getTableName());

        // 添加查询条件
        List<Object> params = new ArrayList<>();
        if (conditions != null && !conditions.isEmpty()) {
            sql.append(" WHERE ");
            sql.append(conditions.entrySet().stream()
                    .map(e -> {
                        params.add(e.getValue());
                        return e.getKey() + " = ?";
                    })
                    .collect(Collectors.joining(" AND ")));
        }

        // 添加分组
        if (groupBy != null && !groupBy.isEmpty()) {
            sql.append(" GROUP BY ").append(String.join(", ", groupBy));
        }

        return jdbcTemplate.queryForList(sql.toString(), params.toArray());
    }

    @Override
    @Timed(value = "form.data.related", description = "关联数据查询的耗时")
    @Cacheable(value = "formRelated", key = "#form.id + '_' + #recordId + '_' + #relationTypes")
    public Map<String, List<Map<String, Object>>> getRelatedData(
            CustomForm form,
            Long recordId,
            List<String> relationTypes) {

        Map<String, List<Map<String, Object>>> result = new HashMap<>();

        // 查询关联关系
        List<FormRelation> relations = form.getSourceRelations().stream()
                .filter(r -> relationTypes.contains(r.getType().name()))
                .collect(Collectors.toList());

        for (FormRelation relation : relations) {
            CustomForm targetForm = relation.getTargetForm();
            Optional<FormTableStructure> opt = Optional.ofNullable(tableStructureMapper.findByFormIdAndStatus(targetForm.getId(), "ACTIVE"));
            if (opt.isEmpty()) {
                throw new RuntimeException("目标表单未初始化");
            }
            FormTableStructure targetStructure = opt.get();

            // 解析关联条件
            Map<String, Object> conditions = null;
            try {
                conditions = objectMapper.readValue(
                        relation.getConditions(), Map.class);
            } catch (JsonProcessingException e) {
                throw new RuntimeException(e);
            }

            // 构建查询SQL
            StringBuilder sql = new StringBuilder()
                    .append("SELECT t.* FROM ")
                    .append(targetStructure.getTableName())
                    .append(" t WHERE ");

            List<Object> params = new ArrayList<>();
            sql.append(conditions.entrySet().stream()
                    .map(e -> {
                        params.add(e.getValue());
                        return "t." + e.getKey() + " = ?";
                    })
                    .collect(Collectors.joining(" AND ")));

            List<Map<String, Object>> relatedData = jdbcTemplate
                    .queryForList(sql.toString(), params.toArray());

            result.put(relation.getName(), relatedData);
        }

        return result;
    }

    private boolean isCommonField(FormField field) {
        return !field.isCustomized();
        // switch (field.getType()) {
        // case TEXT:
        // case NUMBER:
        // case DATE:
        // case TIME:
        // case DATETIME:
        // case SELECT:
        // case RADIO:
        // case CHECKBOX:
        // return true;
        // default:
        // return false;
        // }
    }

    private String generateInsertSql(String tableName, Map<String, Object> fields) {
        StringBuilder sql = new StringBuilder()
                .append("INSERT INTO ").append(tableName).append(" (");

        sql.append(String.join(",", fields.keySet()));
        sql.append(") VALUES (");
        sql.append(fields.values().stream()
                .map(v -> "?")
                .collect(Collectors.joining(",")));
        sql.append(") RETURNING id");

        return sql.toString();
    }

    private String generateUpdateSql(String tableName, Map<String, Object> fields, Long recordId) {
        StringBuilder sql = new StringBuilder()
                .append("UPDATE ").append(tableName).append(" SET ");

        sql.append(fields.keySet().stream()
                .map(k -> k + " = ?")
                .collect(Collectors.joining(",")));
        // sql.append(fields.entrySet().stream().map(entry-> entry.getKey()+" =
        // "+entry.getValue())
        // .collect(Collectors.joining(",")));

        sql.append(" WHERE id = " + recordId);

        return sql.toString();
    }

    private void saveFieldValues(CustomForm form, Long recordId, Map<String, Object> fields) {
        for (Map.Entry<String, Object> entry : fields.entrySet()) {
            FormField field = form.getFields().stream()
                    .filter(f -> f.getCode().equals(entry.getKey()))
                    .findFirst()
                    .orElseThrow(() -> new RuntimeException("字段不存在: " + entry.getKey()));

            FormFieldValue value = fieldValueMapper.selectOneByQuery(QueryWrapper.create().eq("form_id", form.getId())
                    .eq("record_id", recordId).eq("field_id", field.getId()));
            if (value == null) {
                value = new FormFieldValue();
                value.setFormId(form.getId());
                value.setRecordId(recordId);
                value.setFieldId(field.getId());
                setFieldValue(value, field, entry.getValue());
                fieldValueMapper.insert(value);
            } else {
                setFieldValue(value, field, entry.getValue());
                fieldValueMapper.update(value);
            }
        }
    }

    private void setFieldValue(FormFieldValue value, FormField field, Object fieldValue) {
        if (fieldValue == null)
            return;

        switch (field.getType()) {
            case TEXT:
            case TEXTAREA:
            case SELECT:
            case RADIO:
            case CHECKBOX:
                value.setTextValue(fieldValue.toString());
                break;
            case NUMBER:
                if (fieldValue instanceof Number) {
                    value.setNumberValue(((Number) fieldValue).longValue());
                }
                break;
            case DATE:
            case TIME:
            case DATETIME:
                if (fieldValue instanceof LocalDateTime) {
                    value.setDateValue((LocalDateTime) fieldValue);
                }
                break;
            default:
                value.setTextValue(fieldValue.toString());
        }
    }

    private Object getFieldValue(FormFieldValue value) {
        switch (value.getField().getType()) {
            case TEXT:
            case TEXTAREA:
            case SELECT:
            case RADIO:
            case CHECKBOX:
                return value.getTextValue();
            case NUMBER:
                return value.getNumberValue();
            case DATE:
            case TIME:
            case DATETIME:
                return value.getDateValue();
            default:
                return value.getTextValue();
        }
    }

    private void saveExtraData(CustomForm form, Long recordId, Map<String, Object> extraData) {
        try {
            String sql = "UPDATE " + form.getCode().toLowerCase() + "_data SET extra_data = ? WHERE id = ?";
            jdbcTemplate.update(sql, objectMapper.writeValueAsString(extraData), recordId);
        } catch (Exception e) {
            log.error("保存额外数据失败", e);
            throw new RuntimeException("保存额外数据失败", e);
        }
    }

    private void updateExtraData(CustomForm form, Long recordId, Map<String, Object> extraData) {
        try {
            String sql = "UPDATE " + form.getCode().toLowerCase() + "_data SET extra_data = ? WHERE id = ?";
            jdbcTemplate.update(sql, objectMapper.writeValueAsString(extraData), recordId);
        } catch (Exception e) {
            log.error("更新额外数据失败", e);
            throw new RuntimeException("更新额外数据失败", e);
        }
    }
}